package by.epam.communalservices.dao.jdbc;


import java.sql.*;
import java.util.*;

import org.apache.log4j.Logger;

import by.epam.communalservices.dao.DaoUser;
import by.epam.communalservices.entity.User;
import by.epam.communalservices.exception.JkhDaoException;

public class JdbcDaoUser implements DaoUser {

    private Connection con;

    private final String COLUMN_USER_ID = "user_id";

    private final String COLUMN_LOGIN = "login";

    private final String COLUMN_PASSWD = "password";
    
    static Logger JdbcDaoUserLoger = Logger.getLogger(JdbcDaoUser.class.getName());

    public JdbcDaoUser(Connection con) throws JkhDaoException {
        try {
            if (!con.isClosed())
                this.con = con;
            else {
                throw new JkhDaoException("Connection error. Connection is not open", new Exception());
            }
        } catch (JkhDaoException jex) {
            throw jex;
        } catch (SQLException sx) {
            JdbcDaoUserLoger.error("Connection error",sx);
            sx.printStackTrace();
            throw new JkhDaoException("Unknown connection error", new Exception());

        }
    }

    public Long create(User user) {
        JdbcDaoUserLoger.debug("Long create(User user)");
        JdbcDaoUserLoger.info("method create user");
        Long genid = null;
        String sql = "INSERT INTO public.user(login,password) VALUES (?,?)";
        ResultSet result = null;
        PreparedStatement pstate = null;
        JdbcDaoUserLoger.debug("Long create(User user)");
        JdbcDaoUserLoger.info("method adds a user");
        try {
            try {
                pstate = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                pstate.setString(1, user.getLogin());
                pstate.setString(2, user.getPassw());
                pstate.execute();
                result = pstate.getGeneratedKeys();
                if (result.next()) {
                    genid = result.getLong(1);
                } else {
                    throw new SQLException();
                }
            } catch (SQLException ex) {
                JdbcDaoUserLoger.error("query execute error",ex);
                ex.printStackTrace();
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoUserLoger.error("Error close result",ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoUserLoger.error("Error close statement",ex);
                    ex.printStackTrace();
                }
        }
        return genid;
    }

    public User read(Long id) {
        JdbcDaoUserLoger.debug("User read(Long id)");
        JdbcDaoUserLoger.info("method reads the user");
        String sql = "SELECT * FROM public.user WHERE user_id = ?";
        User usr = null;
        ResultSet result = null;
        PreparedStatement pstate = null;
        try {
            try {
                pstate = con.prepareStatement(sql);
                pstate.setLong(1, id);
                result = pstate.executeQuery();
            } catch (SQLException ex) {
                JdbcDaoUserLoger.error("query execute error",ex);
                ex.printStackTrace();
            }
            try {
                if (result.next()) {
                    usr = new User(result.getLong(COLUMN_USER_ID), result.getString(COLUMN_LOGIN), result
                            .getString(COLUMN_PASSWD));
                } else {
                    throw new SQLException();
                }
            } catch (SQLException ex) {
                JdbcDaoUserLoger.error("Record not found",ex);
                ex.printStackTrace();
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoUserLoger.error("Error close result",ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoUserLoger.error("Error close statement",ex);
                    ex.printStackTrace();
                }
        }
        return usr;
    }

    public void delete(Long id) {
        JdbcDaoUserLoger.debug("void delete(Long id)");
        JdbcDaoUserLoger.info("method delete the user");
        String sql = "DELETE FROM public.user where user_id = ?";
        PreparedStatement pstate = null;
        try {
            try {
                pstate = con.prepareStatement(sql);
                pstate.setLong(1, id);
                pstate.execute();
            } catch (SQLException ex) {
                JdbcDaoUserLoger.error("query execute error",ex);
                ex.printStackTrace();
            }
        } finally {
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoUserLoger.error("Error close statement",ex);
                    ex.printStackTrace();
                }
        }
    }

    public void update(Long id, User user) {
        JdbcDaoUserLoger.debug("update(Long id, User user)");
        JdbcDaoUserLoger.info("method update the user");
        PreparedStatement pstate = null;
        String sql = "UPDATE public.user SET login =?, password = ? where user_id = ?";
        try {
            try {
                pstate = con.prepareStatement(sql);
                pstate.setString(1, user.getLogin());
                pstate.setString(2, user.getPassw());
                pstate.setLong(3, id);
                pstate.execute();
            } catch (SQLException ex) {
                JdbcDaoUserLoger.error("query execute error",ex);
                ex.printStackTrace();
            }
        } finally {
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoUserLoger.error("Error close statement",ex);
                    ex.printStackTrace();
                }
        }
    }

    public List<User> findAll() {
        @SuppressWarnings("unchecked")
        List<User> list = Collections.EMPTY_LIST;
        JdbcDaoUserLoger.debug("List<User> findAll()");
        JdbcDaoUserLoger.info("method returns a list of users");
        Statement state = null;
        ResultSet result = null;
        try {
            try {
                String sql = "SELECT * FROM public.user ORDER BY user_id";
                state = con.createStatement();
                result = state.executeQuery(sql);
                list = new ArrayList<User>();
                while (result.next()) {
                    User user = new User(result.getLong(COLUMN_USER_ID), result.getString(COLUMN_LOGIN), result
                            .getString(COLUMN_PASSWD));
                    list.add(user);
                }
            } catch (SQLException ex) {
                JdbcDaoUserLoger.error("Error compiling the list",ex);
                ex.printStackTrace(System.out);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoUserLoger.error("Error close result",ex);
                    ex.printStackTrace();
                }
            if (state != null)
                try {
                    state.close();
                } catch (SQLException ex) {
                    JdbcDaoUserLoger.error("Error close statement",ex);
                    ex.printStackTrace();
                }
        }
        return list;
    }
}
